<?php


namespace App\Admin\Extensions;

use DateTime;
use Dcat\Admin\Grid\Exporters\AbstractExporter;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use Illuminate\Support\Arr;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class GzdKaoQinExcelExpoter extends AbstractExporter
{


    public function export()
    {

        $data = collect($this->buildData(1, 10000))->map(function ($item) {
            return Arr::only($item, [
                                        'name',
                                        'time_slot',
                                        'k1',
                                        'k1',
                                        'k2',
                                        'k3',
                                        'k4',
                                        'k5',
                                        'k6',
                                        'k7',
                                        'k8',
                                        'k9',
                                        'k10',
                                        'k11',
                                        'k12',
                                        'k13',
                                        'k14',
                                        'k15',
                                        'k16',
                                        'k17',
                                        'k18',
                                        'k19',
                                        'k20',
                                        'k21',
                                        'k22',
                                        'k23',
                                        'k24',
                                        'k25',
                                        'k26',
                                        'k27',
                                        'k28',
                                        'k29',
                                        'k30',
                                        'k31',
                                        'sum',
                                        'remarks'
                                    ]);
        });

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        //设置sheet的名字  两种方法
        $sheet->setTitle('sheet1');
//        $spreadsheet->getActiveSheet()->setTitle('Hello');
        //设置第一行小标题
        $k = 1;
        $sheet->setCellValue('A' . $k, '序号');
        $sheet->setCellValue('B' . $k, '姓名');
        $sheet->setCellValue('C' . $k, '时段');
        $sheet->setCellValue('D' . $k, '1');
        $sheet->setCellValue('E' . $k, '2');
        $sheet->setCellValue('F' . $k, '3');
        $sheet->setCellValue('G' . $k, '4');
        $sheet->setCellValue('H' . $k, '5');
        $sheet->setCellValue('I' . $k, '6');
        $sheet->setCellValue('J' . $k, '7');
        $sheet->setCellValue('K' . $k, '8');
        $sheet->setCellValue('L' . $k, '9');
        $sheet->setCellValue('M' . $k, '10');
        $sheet->setCellValue('N' . $k, '11');
        $sheet->setCellValue('O' . $k, '12');
        $sheet->setCellValue('P' . $k, '13');
        $sheet->setCellValue('Q' . $k, '14');
        $sheet->setCellValue('R' . $k, '15');
        $sheet->setCellValue('S' . $k, '16');
        $sheet->setCellValue('T' . $k, '17');
        $sheet->setCellValue('U' . $k, '18');
        $sheet->setCellValue('V' . $k, '19');
        $sheet->setCellValue('W' . $k, '20');
        $sheet->setCellValue('X' . $k, '21');
        $sheet->setCellValue('Y' . $k, '22');
        $sheet->setCellValue('Z' . $k, '23');
        $sheet->setCellValue('AA' . $k, '24');
        $sheet->setCellValue('AB' . $k, '25');
        $sheet->setCellValue('AC' . $k, '26');
        $sheet->setCellValue('AD' . $k, '27');
        $sheet->setCellValue('AE' . $k, '28');
        $sheet->setCellValue('AF' . $k, '29');
        $sheet->setCellValue('AG' . $k, '30');
        $sheet->setCellValue('AH' . $k, '31');
        $sheet->setCellValue('AI' . $k, '总计');
        $sheet->setCellValue('AJ' . $k, '备注');


        // 设置个表格宽度
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('W')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('X')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('Y')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('Z')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('AA')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('AB')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('AC')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('AD')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('AE')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('AF')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('AG')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('AH')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('AI')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('AJ')->setWidth(10);

        // 垂直居中

        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('K')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('L')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('M')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('N')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('O')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('P')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('Q')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('R')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('S')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('T')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('U')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('V')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('W')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('X')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('Y')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('Z')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('AA')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('AB')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('AC')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('AD')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('AE')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('AF')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('AG')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('AH')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('AI')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('AJ')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

        $info = $data;
        //  设置A单元格的宽度 同理设置每个
//        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5);


        //  A1水平居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        //  将A3到D4合并成一个单元格
        $spreadsheet->getActiveSheet()->mergeCells('A3:D4');
        //  拆分合并单元格
        $spreadsheet->getActiveSheet()->unmergeCells('A3:D4');
        //  将A2到D8表格边框 改变为红色
        $styleArray = [
            'borders' => [
                'outline' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
                    'color' => ['argb' => 'FFFF0000'],
                ],
            ],
        ];
        //  $sheet->getStyle('A2:E8')->applyFromArray($styleArray);
        //  设置超链接
        //  $sheet->setCellValue('D6', 'www.baidu.com');
        //  $spreadsheet->getActiveSheet()->setCellValue('E6', 'www.baidu.com');
        //  循环赋值

        $k = 2;
        $xuhao = 1;
        foreach ($info as $key => $value) {


            if($value['time_slot'] == '上午'){
                $sheet->setCellValueExplicit('A' . $k, $xuhao, 'str');
                $xuhao++;
                $sheet->setCellValueExplicit('B' . $k, $value['name'], 'str');
                $total = 0;
                $hebing_zuo_A = 'A'.$k;
                $hebing_zuo_B = 'B'.$k;
                $hebing_zuo_AI = 'AI'.$k;
                $hebing_zuo_AJ = 'AJ'.$k;
            }
            $sheet->setCellValueExplicit('C' . $k, $value['time_slot'], 'str');
            $sheet->setCellValueExplicit('D' . $k, $value['k1'], 'str');
            $sheet->setCellValueExplicit('E' . $k, $value['k2'], 'str');
            $sheet->setCellValueExplicit('F' . $k, $value['k3'], 'str');
            $sheet->setCellValueExplicit('G' . $k, $value['k4'], 'str');
            $sheet->setCellValueExplicit('H' . $k, $value['k5'], 'str');
            $sheet->setCellValueExplicit('I' . $k, $value['k6'], 'str');
            $sheet->setCellValueExplicit('J' . $k, $value['k7'], 'str');
            $sheet->setCellValueExplicit('K' . $k, $value['k8'], 'str');
            $sheet->setCellValueExplicit('L' . $k, $value['k9'], 'str');
            $sheet->setCellValueExplicit('M' . $k, $value['k10'], 'str');
            $sheet->setCellValueExplicit('N' . $k, $value['k11'], 'str');
            $sheet->setCellValueExplicit('O' . $k, $value['k12'], 'str');
            $sheet->setCellValueExplicit('P' . $k, $value['k13'], 'str');
            $sheet->setCellValueExplicit('Q' . $k, $value['k14'], 'str');
            $sheet->setCellValueExplicit('R' . $k, $value['k15'], 'str');
            $sheet->setCellValueExplicit('S' . $k, $value['k16'], 'str');
            $sheet->setCellValueExplicit('T' . $k, $value['k17'], 'str');
            $sheet->setCellValueExplicit('U' . $k, $value['k18'], 'str');
            $sheet->setCellValueExplicit('V' . $k, $value['k19'], 'str');
            $sheet->setCellValueExplicit('W' . $k, $value['k20'], 'str');
            $sheet->setCellValueExplicit('X' . $k, $value['k21'], 'str');
            $sheet->setCellValueExplicit('Y' . $k, $value['k22'], 'str');
            $sheet->setCellValueExplicit('Z' . $k, $value['k23'], 'str');
            $sheet->setCellValueExplicit('AA' . $k, $value['k24'], 'str');
            $sheet->setCellValueExplicit('AB' . $k, $value['k25'], 'str');
            $sheet->setCellValueExplicit('AC' . $k, $value['k26'], 'str');
            $sheet->setCellValueExplicit('AD' . $k, $value['k27'], 'str');
            $sheet->setCellValueExplicit('AE' . $k, $value['k28'], 'str');
            $sheet->setCellValueExplicit('AF' . $k, $value['k29'], 'str');
            $sheet->setCellValueExplicit('AG' . $k, $value['k30'], 'str');
            $sheet->setCellValueExplicit('AH' . $k, $value['k31'], 'str');

            $total += $value['sum'];
            if($value['time_slot'] == '加班'){
                $ke = $k-2;
                $sheet->setCellValueExplicit('AI' . $ke, $total, 'str');

            }

            if($value['time_slot'] == '加班'){
                $hebing_you_A = 'A'.$k;
                $hebing_you_B = 'B'.$k;
                $hebing_you_AI = 'AI'.$k;
                $hebing_you_AJ = 'AJ'.$k;

                $spreadsheet->getActiveSheet()->mergeCells($hebing_zuo_A.':'.$hebing_you_A);
                $spreadsheet->getActiveSheet()->mergeCells($hebing_zuo_B.':'.$hebing_you_B);
                $spreadsheet->getActiveSheet()->mergeCells($hebing_zuo_AI.':'.$hebing_you_AI);
                $spreadsheet->getActiveSheet()->mergeCells($hebing_zuo_AJ.':'.$hebing_you_AJ);
            }

            $sheet->setCellValueExplicit('AJ' . $k, $value['remarks'], 'str');

            $sheet->getRowDimension($k)->setRowHeight(15);
            $k++;
        }
        $file_name = date('Y-m-d', time()) . rand(1000, 9999);


        //保存的路径可自行设置
//        $file_name = '../'.$file_name . ".xlsx";
//        $writer->save($file_name);
        //  第二种直接页面上显示下载
        $file_name = $file_name . ".xlsx";
        header('Content-Type: application/vnd.ms-excel');
        header('Expires: 0');
        header('Pragma: public');
        header('Content-Disposition: attachment;filename="' . $file_name . '"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        //  注意createWriter($spreadsheet, 'Xls') 第二个参数首字母必须大写
        ob_end_clean();
        $writer->save('php://output');
        exit();
    }

    public function getClient(){
        $client = new Client();
        return $client;
    }

    public static function curlGet($url)
    {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_HEADER, 0);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); // 这个是重点 请求https。
        $data = curl_exec($ch);
        curl_close($ch);
        return $data;
    }


}
